package com.datasecops.scannerservices.operation;

import com.datasecops.scannerservices.db.DBHelper;
import com.datasecops.scannerservices.entity.CommonParam;
import com.datasecops.scannerservices.enumerate.DBType;
import com.datasecops.scannerservices.service.ScanDataProcessServer;
import com.datasecops.scannerservices.util.DBFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;

/**
 * 数据库公操作类
 *
 * @author: SunLingzhi
 * @time: 2022/8/16 10:42
 */
@Service
@Slf4j
public class DBCommonOperation {

    //每一批次定1000行
    private int batchLine = 1000;

    @Resource
    private ScanDataProcessServer dataProcessServer;


    /**
     * 获取数据库下面所有的表
     *
     * @param
     * @return
     * @Description
     * @author SunLingzhi
     * @time 2022/8/18 10:52
     */
    public List<String> getTablesByDBName(CommonParam param,String Instance,String Model) {
        String sourceKey = DBHelper.getSourceMapKey(param.getIp(), param.getPort(),param.getInstance());
        DBHelper dbHelper = DBFactory.getConnectDataSource(param.getTypeEnum(), param.getIp(), param.getPort(), param.getUserName(), param.getPassword(),Instance);
        if (null == dbHelper) {
            return null;
        }
        Connection connection = dbHelper.getConnection(sourceKey);
        PreparedStatement statement = null;
        List<String> tableNames = new ArrayList<>();
        try {
            String querySql = getQueryTableNameSql(param.getTypeEnum(), param.getUserName(), param.getDbName(),Model,Instance);
            log.error("数据库查询表的语句是:"+querySql);
            //切换数据库
            //mysql\oracle需先切换
            if (param.getTypeEnum() == DBType.MYSQL || param.getTypeEnum() == DBType.SQLSERVER || param.getTypeEnum() == DBType.MARIADB
                    || param.getTypeEnum() == DBType.TIDB || param.getTypeEnum() == DBType.OCEANBASE|| param.getTypeEnum() == DBType.HOTDB ) {//|| param.getTypeEnum() == DBType.POSTGRESQL
                statement = connection.prepareStatement(getUseSql( param.getDbName()));
                statement.execute();
            }
            if (param.getTypeEnum() == DBType.HIVE){
                statement = connection.prepareStatement(getUseSql2( param.getDbName()));
                statement.execute();
            }
            statement = connection.prepareStatement(querySql);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                if (param.getTypeEnum() == DBType.SQLSERVER || param.getTypeEnum() == DBType.POSTGRESQL ||param.getTypeEnum() == DBType.DB2 || param.getTypeEnum() == DBType.RDS_POSTGRESQL){
                    log.error("总行数1");
                    statement = connection.prepareStatement("SELECT COUNT(*) FROM " +"\""+Model+"\""+"." +"\""+resultSet.getObject(1).toString()+"\"");
                }else if (param.getTypeEnum() == DBType.ORACLE || param.getTypeEnum() ==DBType.DM){
                    log.error("总行数2");
                    statement = connection.prepareStatement("SELECT COUNT(*) FROM "+"\"" +resultSet.getObject(1).toString()+"\"");
                } else {
                    log.error("总行数3");
                    statement = connection.prepareStatement("SELECT COUNT(*) FROM " +resultSet.getObject(1).toString());
                }

                ResultSet result = statement.executeQuery();//这以下是新增的
                result.next();
                int anInt = result.getInt(1);
                tableNames.add(resultSet.getObject(1).toString()+"."+anInt);
                //linage.add(String.valueOf(anInt));
            }
        } catch (Exception e) {
            log.error("Failed Method getTablesByDBName error :" + e.getMessage());
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                    DBHelper.closeConnection(connection);
                } catch (SQLException e) {
                    log.error("Failed Method getTablesByDBName statement.close error :" + e.getMessage());
                }
            }
        }
        return tableNames;
    }

    /**
     *扫描数据库下所有的模式
     * @Description
     * @author GeXiaoLong
     * @time 2023/3/2 9:29
     * @param
     * @return
     */
    @SuppressWarnings("all")
    public List<String> getModelByDBName(CommonParam param,String Instance,String Model) {
        String sourceKey = DBHelper.getSourceMapKey(param.getIp(), param.getPort(),param.getInstance());
        DBHelper dbHelper = DBFactory.getConnectDataSource(param.getTypeEnum(), param.getIp(), param.getPort(), param.getUserName(), param.getPassword(),Instance);
        if (null == dbHelper) {
            return null;
        }
        Connection connection = dbHelper.getConnection(sourceKey);
        PreparedStatement statement = null;
        List<String> models = new ArrayList<>();
        try {
            String querySql = getQueryModelSql(param.getTypeEnum(), param.getUserName(), param.getDbName(),Model,Instance);
            //log.info("数据库查询表的语句是: "  +querySql);
            //切换数据库
            //mysql\oracle需先切换
            if (param.getTypeEnum() == DBType.MYSQL
                    || param.getTypeEnum().name() ==DBType.RDS_MYSQL.name()//新增的
                    || param.getTypeEnum() == DBType.SQLSERVER
                    || param.getTypeEnum().name() == DBType.RDS_SQLSERVER.name()//新增的
                    || param.getTypeEnum() == DBType.MARIADB
                    || param.getTypeEnum().name() == DBType.RDS_MARIADB.name()//新增的
                    || param.getTypeEnum() == DBType.TIDB
                    || param.getTypeEnum() == DBType.OCEANBASE
                    || param.getTypeEnum() == DBType.HOTDB ) {//|| param.getTypeEnum() == DBType.POSTGRESQL
                statement = connection.prepareStatement(getUseSql( param.getDbName()));
                statement.execute();
            }
            if (param.getTypeEnum() == DBType.HIVE){
                statement = connection.prepareStatement(getUseSql2( param.getDbName()));
                statement.execute();
            }

            statement = connection.prepareStatement(querySql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                models.add(resultSet.getObject(1).toString());
            }
        } catch (Exception e) {
            log.error("Failed Method getTablesByDBName error :" + e.getMessage());
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                    DBHelper.closeConnection(connection);
                } catch (SQLException e) {
                    log.error("Failed Method getTablesByDBName statement.close error :" + e.getMessage());
                }
            }
        }
        return models;
    }

    /**
     * 获取DB数据返回
     *
     * @param
     * @param commonParam
     * @param typeEnum
     * @param jobId
     * @param jobName
     * @param scannerID
     * @param linage
     * @return
     * @Description
     * @author SunLingzhi
     * @time 2022/8/16 10:43
     */
    @SuppressWarnings("all")
    public boolean getDBResult(CommonParam param, CommonParam commonParam, DBType typeEnum, String jobId, String jobName, String scannerID, String linage) {
        String sourceKey = DBHelper.getSourceMapKey(param.getIp(), param.getPort(),param.getInstance());
        DBHelper dbHelper = DBFactory.getConnectDataSource(param.getTypeEnum(), param.getIp(), param.getPort(), param.getUserName(), param.getPassword(),param.getInstance());
        if (null == dbHelper) {
            return false;
        }
        Connection connection = dbHelper.getConnection(sourceKey);
        PreparedStatement statement = null;
        LinkedHashMap<String, LinkedList<String>> sourceDataMap = new LinkedHashMap<>();

        try {
            //解决OOM问题(数据库量太大会内存溢出)
            statement = connection.prepareStatement(param.getSql(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            statement.setFetchSize(batchLine);//批次大小
            statement.setFetchDirection(ResultSet.FETCH_FORWARD);//处理记录集中行的顺序是由前到后，即从第一行开始处理，一直到最后一行

            //statement.setFetchSize(Integer.MIN_VALUE);
            //statement.setFetchDirection(ResultSet.FETCH_REVERSE);
            if (null != param.getMaximumRows()){
                statement.setMaxRows(param.getMaximumRows());
            }
            ResultSet resultSet = statement.executeQuery();
            //resultSet.setFetchSize(Integer.MIN_VALUE);
            ResultSetMetaData metaData = resultSet.getMetaData();

            //获取label
            int columnCount = metaData.getColumnCount();
            List<String> labelArray = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                labelArray.add(metaData.getColumnName(i));
            }

            LinkedList<String> colsArray;
            String title;

            int count = 0;
            int batch = 0;
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    title = labelArray.get(i);
                    Object obj = resultSet.getObject(title);
                    if (obj == null) {
                        obj = "";
                    }
                    colsArray = sourceDataMap.getOrDefault(title, new LinkedList<>());
                    colsArray.add(obj.toString());
                    sourceDataMap.put(title, colsArray);
                }
                count++;
                if (count % batchLine == 0) {//每1000行，为一个批次
                    //数据操作
                    batch++;
                    //todo
                    dataProcessServer.tradingData(param,sourceDataMap,batch,commonParam,typeEnum,jobId,jobName,scannerID,linage);
                    sourceDataMap.clear();
                }
                /*if (count >= param.getMaximumRows()) {//达到限定行数直接跳出遍历
                    break;
                }*/
            }

            //处理未满一千行的
            if (sourceDataMap.size() > 0) {
                batch++;
                dataProcessServer.tradingData(param,sourceDataMap,batch, commonParam, typeEnum, jobId, jobName,
                        scannerID, linage);
                sourceDataMap.clear();
            }

        } catch (Exception e) {
            log.error("Failed Method getDBResult error :" + e.getMessage());
            return false;
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                    DBHelper.closeConnection(connection);
                } catch (SQLException e) {
                    log.error("Failed Method getDBResult statement.close error :" + e.getMessage());
                }
            }
        }
        return true;
    }


    /**
     * get查询表名称sql语句
     *
     * @param
     * @return
     * @Description
     * @author SunLingzhi
     * @time 2022/8/18 11:27
     */
    private String getQueryTableNameSql(DBType type, String user, String dbName,String Model,String Integer) {
        switch (type) {
            case MYSQL:
            case MARIADB :
            case TIDB:
            case OCEANBASE:
            case HOTDB:
            case RDS_MYSQL:
            case RDS_MARIADB:
                return "SHOW TABLES;";
            case ORACLE:
                return "SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = '" + dbName.toUpperCase() + "'";//加上";"会异常
            case SQLSERVER:
            case RDS_SQLSERVER:
                //return "SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U';";
                return "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="+"\'"+Model+"\'";
            case POSTGRESQL:
            case OPENGAUSS:
            case RDS_POSTGRESQL:
                return "select tablename from pg_tables where schemaname="+"\'"+Model+"\'";
            //return "SELECT * FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema')";
            //SELECT DISTINCT schemaname FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
            case MONGODB:
                return "show collections";
            case DM:
                return "SELECT table_name FROM dba_tables WHERE owner = "+"\'"+Integer+"\'";
            case HIVE:
                return "SHOW TABLES";
            case DB2:
                return "SELECT name FROM sysibm.systables WHERE  creator="+"\'"+Model+"\'";
            case  OSCAR :
                return "SELECT TABLE_NAME FROM INFO_SCHEM.ALL_ALL_TABLES WHERE TABLE_TYPE_OWNER = "+"\'"+Model+"\'";
        }
        return user;
    }
     /**
      *获取到数据库模式名
      * @Description
      * @author GeXiaoLong
      * @time 2023/3/2 11:48
      * @param
      * @return
      */
    private String getQueryModelSql(DBType type, String user, String dbName,String Model,String Integer) {
        switch (type) {
            case POSTGRESQL:
            case RDS_POSTGRESQL:
                //return "select tablename from pg_tables where schemaname="+"\'"+Model+"\'";
                return "SELECT DISTINCT schemaname FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema')";
            case MONGODB:
                return "show collections";
            case SQLSERVER:
            case RDS_SQLSERVER:
                return "SELECT name FROM sys.schemas WHERE name NOT IN ('INFORMATION_SCHEMA', 'sys') AND principal_id <4";
            case DB2:
              /*  return "SELECT schemaname FROM SYSCAT.SCHEMATA WHERE SCHEMANAME != 'ADMINISTRATOR' AND SCHEMANAME !=  'SYSPUBLIC' AND SCHEMANAME !=  'SYSTOOLS'";*/

            return  "SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE SCHEMANAME NOT LIKE 'SYS%' AND SCHEMANAME <> 'ADMINISTRATOR'";
        }
        return user;
    }

    /**
     * 切换数据库
     *
     * @param
     * @return
     * @Description
     * @author SunLingzhi
     * @time 2022/8/18 13:51
     */
    private String getUseSql(String dbName) {
        return "USE " + dbName + ";";
    }

    private String getUseSql2(String dbName) {
        return "USE " + dbName;
    }

    public static void main(String[] args) {
        //new DBCommonOperation().getTablesByDBName(DBType.MYSQL, "10.26.18.59", 3306, "root", "sc.9102", "dsm");
        // new DBCommonOperation().getTablesByDBName(DBType.ORACLE,"localhost", 1521, "root", "whjx8888","dsm");
        // new DBCommonOperation().getTablesByDBName(DBType.SQLSERVER, "localhost", 1433, "root", "whjx8888", "dsm");
    }


}